package PRSystem_DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Scanner;

import PRSystem_Main.PRSystem_connection;
import PRSystem_VO.PRSystem_ClientVO;
import PRSystem_VO.PRSystem_PerformanceVO;

public class PRSystemPerformanceDAOImpl implements PRSystemPerformanceDAO {

	@Override
	public String insertPerformance(PRSystem_PerformanceVO vo_ob_user) {
		// -INSERT,UPDATE,DELETE 문은 executeUpdate() 를 사용한다.
		// -executeQuery()는 실제데이타를 반환하는 반면, executeUpdate()는 엡뎃한 건수를 반환
		// -PreparedStatement를 사용하면 가독성이 좋은 코딩을 할 수 있다.
		Connection conn = null;
		PreparedStatement pstmt = null;
		String result = "";
		String sql = " insert into Performance (perno,title,price_R,price_S,price_A,actor,summary,pno) values(seq_perno.nextval,?,?,?,?,?,?,?)";
		Scanner sc = new Scanner(System.in);

		System.out.println("데이터를 입력합니다.");
		System.out
				.println("공연제목, R석 가격, S석 가격, A석 가격, 출연진, 시놉시스, 공연장 번호 순으로 데이터를 입력하세요");
		PRSystem_PerformanceVO prpvo = new PRSystem_PerformanceVO(sc.next(),
				sc.nextInt(), sc.nextInt(),sc.nextInt(),sc.next(), sc.next(),sc.nextInt());

		PRSystem_connection prcon = new PRSystem_connection();
		try {
 			conn = prcon.makeConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, prpvo.getTitle());
			// pstmt.setBytes(2, prpvo.getOimg());
			pstmt.setInt(2, prpvo.getPrice_r());
			pstmt.setInt(3, prpvo.getPrice_s());
			pstmt.setInt(4, prpvo.getPrice_a());
			pstmt.setString(5, prpvo.getActor());
			pstmt.setString(6, prpvo.getSummary());
			pstmt.setInt(7, prpvo.getPno());
			
			int cnt = pstmt.executeUpdate();

			if (cnt > 0)
				result = cnt + "건의 공연정보가 입력되었습니다.";

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			prcon.closeConn(conn, pstmt);
		}
		return result;
	}

	@Override
	public ArrayList<PRSystem_PerformanceVO> selectPerformance() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from performance order by perNo";
		System.out.println("전체 공연정보");
		ArrayList<PRSystem_PerformanceVO> prpvos = new ArrayList<>();
		
		PRSystem_connection prc = new PRSystem_connection();
		try {
			conn = prc.makeConn();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			prpvos = new ArrayList<>();
			while (rs.next()) {
				PRSystem_PerformanceVO vo = new PRSystem_PerformanceVO();
				vo.setPerNo(rs.getInt(1));
				vo.setTitle(rs.getString(2));
				vo.setOimg(rs.getBytes(3));
				vo.setPrice_r(rs.getInt(4));
				vo.setPrice_s(rs.getInt(5));
				vo.setPrice_a(rs.getInt(6));
				vo.setActor(rs.getString(7));
				vo.setSummary(rs.getString(8));
				vo.setPno(rs.getInt(9));
				prpvos.add(vo);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			prc.closeConn(conn, pstmt, rs);
		}

		return prpvos;
	}

	@Override
	public ArrayList<PRSystem_PerformanceVO> selectOnePerformance(int perno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// *공연 조회 검색 - 공연번호, 공연제목, 공연일, 공연장 이름
		String sqlSelectPerno = "select * from Performance where perNo = ? order by perNo";
		// String sqlSelectTitle =
		// "select * from Performance where title = ? order by perNo";
		// String sqlSelectDate =
		// "select * from Performance where date = ? order by perNo";
		// String sqlSelectpName =
		// "select * from Performance where pName =? order by perNo";


	
		ArrayList<PRSystem_PerformanceVO> prpvos = new ArrayList<>();
		PRSystem_connection prc = new PRSystem_connection();
		try {
			conn = prc.makeConn();
			pstmt = conn.prepareStatement(sqlSelectPerno);
			              pstmt.setInt(1, perno);
			rs = pstmt.executeQuery();

			prpvos = new ArrayList<>();
			while (rs.next()) {
				PRSystem_PerformanceVO vo = new PRSystem_PerformanceVO();
				vo.setPerNo(rs.getInt(1));
				vo.setTitle(rs.getString(2));
				vo.setOimg(rs.getBytes(3));
				vo.setPrice_r(rs.getInt(4));
				vo.setPrice_s(rs.getInt(5));
				vo.setPrice_a(rs.getInt(6));
				vo.setActor(rs.getString(7));
				vo.setSummary(rs.getString(8));
				vo.setPno(rs.getInt(9));
				
				prpvos.add(vo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			prc.closeConn(conn, pstmt, rs);
		}
 
		return prpvos;
	}

	@Override
	public String updatePerformance(int perno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update performance set title=?, price_r=?,price_s=?,price_a=?, actor=?, summary=?, pno=? where perNo=?";
	
		Scanner sc = new Scanner(System.in);
		String result = "";


		System.out
				.println("공연제목, R석 가격, S석 가격, A석 가격,  출연진, 시놉시스, 공연장 번호 순으로 데이터를 입력하세요");
		PRSystem_PerformanceVO prpvo = new PRSystem_PerformanceVO(sc.next(), sc.nextInt(), sc.nextInt(), sc.nextInt(), sc.next(), sc.next(), sc.nextInt());

		PRSystem_connection prc = new PRSystem_connection();
		try {
			conn = prc.makeConn();
			pstmt = conn.prepareStatement( sql);
			              pstmt.setInt(1, perno);

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, prpvo.getTitle());
			pstmt.setInt(2, prpvo.getPrice_r());
			pstmt.setInt(3, prpvo.getPrice_s());
			pstmt.setInt(4, prpvo.getPrice_a());
			pstmt.setString(5, prpvo.getActor());
			pstmt.setString(6, prpvo.getSummary());
			pstmt.setInt(7, prpvo.getPno());
			pstmt.setInt(8, perno);
			
			
			int cnt = pstmt.executeUpdate();
			if (cnt > 0)
				result = cnt + "건의 공연정보가 수정되었습니다.";

			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			prc.closeConn(conn, pstmt);
		}

		return result;
	}

	@Override
	public String deletePerformance(int perno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from performance where perNo=?";
		Scanner sc = new Scanner(System.in);
		String result = "";
		
	
		PRSystem_connection prc = new PRSystem_connection();
		try {
			conn = prc.makeConn();
			pstmt = conn.prepareStatement( sql);
			pstmt.setInt(1, perno);
			
			int cnt = pstmt.executeUpdate();
			if (cnt > 0)
				result = cnt + "건의 공연정보가 삭제되었습니다.";

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			prc.closeConn(conn, pstmt);
		}

		return result;
	}

}  // public class PRSystemPerformanceDAOImpl implements PRSystemPerformanceDAO 
